package model.dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import domain.PetInfo;

public class PetDao {
	Connection cn = null;
	Statement st = null;
	PreparedStatement pt = null;
	int petid;
	ResultSet rs = null;

	public void init() {
		try {
			cn = DriverManager.getConnection("proxool.DbPool");
			/*System.out.println("Success loading  MySQL Driver...");*/
		} catch (Exception e) {
			System.out.println("Error loading MySQL Driver...");
			e.printStackTrace();
		}
	}

	public void close() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (pt != null) {
				pt.close();
			}
			if (st != null) {
				st.close();
			}
			if (cn != null) {
				cn.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
    
	public boolean searchPet(String name) {
		init();
		int count=0;
		try {
			String url = "select count(*) from pet_info where Pet_Name='"
					+ name + "'";
			st = cn.createStatement();
			rs = st.executeQuery(url);
			if (rs.next()) {
				count=rs.getInt(1);
			}
			if(count==1){
				return true;
			}else{
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			close();
		}

	}
	public Boolean AddPet(String name, int age, String sex, String hobby,
			String place, String comment, File photo, String path, float price) {
		init();
		try {
			FileInputStream in = new FileInputStream(photo);
			String url = "select Pet_Id,Pet_Name,Pet_Age,Pet_Sex,Pet_Hobby,Pet_Produce_Place,Pet_Comment,Pet_Photo,Pet_PhotoName,Pet_Price from pet_info";
			st = cn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = st.executeQuery(url);
			rs.moveToInsertRow();
			rs.updateString(2, name);
			rs.updateInt(3, age);
			rs.updateString(4, sex);
			rs.updateString(5, hobby);
			rs.updateString(6, place);
			rs.updateString(7, comment);
			rs.updateBinaryStream(8, in, in.available());
			rs.updateString(9, path);
			rs.updateFloat(10, price);
			rs.insertRow();
			rs.last();
			petid = rs.getInt(1);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			close();
		}
	}

	public Boolean EditPet(int id, String name, int age, String sex,
			String hobby, String place, String comment, File photo,
			String path, float price) {
		init();
		try {

			String url = "select Pet_Id,Pet_Name,Pet_Age,Pet_Sex,Pet_Hobby,Pet_Produce_Place,Pet_Comment,Pet_Photo,Pet_PhotoName,Pet_Price from pet_info where Pet_Id="
					+ id;
			st = cn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = st.executeQuery(url);
			if (rs.next()) {
				rs.updateString(2, name);
				rs.updateInt(3, age);
				rs.updateString(4, sex);
				rs.updateString(5, hobby);
				rs.updateString(6, place);
				rs.updateString(7, comment);
				if (photo == null)
					;
				else {
					FileInputStream in = new FileInputStream(photo);
					rs.updateBinaryStream(8, in, in.available());
					rs.updateString(9, path);
				}
				rs.updateFloat(10, price);
				rs.updateRow();
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			close();
		}
	}

	public void deletePet(int id, String name) {
		init();
		try {
			String url ="delete from pet_info where Pet_Id=" + id
					+ " and Pet_Name='" + name + "'";
			st = cn.createStatement();
			st.executeUpdate(url);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}

	}

	public InputStream searchPet_Photo(String name) {
		init();
		try {
			String url = "select Pet_Id,Pet_Photo from pet_info where Pet_Name='"
					+ name + "'";
			st = cn.createStatement();
			rs = st.executeQuery(url);
			if (rs.next()) {
				return rs.getBinaryStream(2);
			} else
				return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			close();
		}

	}

	public Boolean insertPetClass(int petID, int class2) {
		init();
		try {
			String url = "insert into class_pet(Pet_Id,ClassTwo_Id) values(?,?)";
			pt = cn.prepareStatement(url);
			pt.setInt(1, petID);
			pt.setInt(2, class2);
			pt.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			close();
		}
	}

	public int searchRowCount() {
		init();
		try {
			String url = "select Pet_Id from pet_info";
			st = cn.createStatement();
			rs = st.executeQuery(url);
			rs.last();
			return rs.getRow();
		} catch (Exception e) {
			return 0;
		} finally {
			close();
		}
	}

	public ArrayList<PetInfo> showPets(int i, int pageSize, int pageNow) {
		init();
		try {
			ArrayList<PetInfo> list = new ArrayList<PetInfo>();
			if (pageSize > 0 && pageNow > 0) {
				st = cn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_UPDATABLE);
				rs = st
						.executeQuery("select pet_info.Pet_Id,Pet_Name,Pet_Age,Pet_Sex,Pet_Hobby,Pet_Produce_Place,Pet_Comment,Pet_Photo,Pet_PhotoName,Pet_Price,ClassTwo_Name from (pet_info left outer join class_pet on pet_info.Pet_Id=class_pet.Pet_Id) left outer join classtwo on class_pet.ClassTwo_Id=classtwo.ClassTwo_Id");
				rs.absolute(i);
				int j = 0;
				do{
					PetInfo pet = new PetInfo();
					pet.setPet_Id(rs.getInt(1));
					pet.setPet_Name(rs.getString(2));
					pet.setPet_Age(rs.getInt(3));
					pet.setPet_Sex(rs.getString(4));
					pet.setPet_Hobby(rs.getString(5));
					pet.setPet_Produce_place(rs.getString(6));
					pet.setPet_Comment(rs.getString(7));
					pet.setPet_Photo(rs.getBinaryStream(8));
					pet.setPet_PhotoName(rs.getString(9));
					pet.setPet_Price(rs.getFloat(10));
					pet.setPet_ClassName(rs.getString(11));
					list.add(pet);
					j++;
				}while(rs.next() && j < pageSize);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			close();
		}

	}

	public int getPetid() {
		return petid;
	}

	public void setPetid(int petid) {
		this.petid = petid;
	}

}
